package com.pks.autoInsurance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import com.pks.autoInsurance.dto.LoginDto;
import com.pks.autoInsurance.dto.OwnerDetailsDto;
import com.pks.autoInsurance.dto.PersonalDetailsDto;
import com.pks.autoInsurance.dto.VehicleDetailsDto;
/*
 * Implementation class of the RegistrationDAO
 */
public class RegistrationDAOImpl extends AutoInsuranceDAO implements
		RegistrationDAO {
	/*
	 * This method is used for creating a new user registration.
	 * 
	 * @see
	 * com.cts.autoInsurance.dao.RegistrationDAO#createUser(com.cts.autoInsurance
	 * .dto.PersonalDetailsDto)
	 */
	@Override
	public boolean createUser(PersonalDetailsDto personalDetailsDtoReq,
			LoginDto loginDto) {
		System.out.println(getClass() + "::::createUser::::begin");
		Connection connection = getConnection();
		String sqlPersonalDetails = "INSERT INTO REGISTRATION VALUES(?,?,?,?,?,?,?,?,?)";
		String sqlUsersLogin = "INSERT INTO USERS_LOGIN VALUES(?,?)";
		String sqlOwnerDetails = "INSERT INTO CUSTOMER_DETAILS VALUES(?,?,?,?,?)";
		String sqlVehicleDetails = "INSERT INTO VEHICLE_DETAILS VALUES(?,?,?,?,?,?,?,?,SYSDATE)";

		OwnerDetailsDto ownerDetailsDto = personalDetailsDtoReq
				.getOwnerDetailsDto();
		VehicleDetailsDto vehicleDetailsDto = personalDetailsDtoReq
				.getVehicleDetailsDto();
		String userId = personalDetailsDtoReq.getUserId();
		try {
			connection.setAutoCommit(false);
			// insert value in registration table
			PreparedStatement preparedStatement = connection
					.prepareStatement(sqlPersonalDetails);
			java.sql.Date dateofBirth = new java.sql.Date(personalDetailsDtoReq
					.getDateOfBirth().getTime());

			preparedStatement.setString(1, userId);
			preparedStatement
					.setString(2, personalDetailsDtoReq.getFirstName());
			preparedStatement.setString(3, personalDetailsDtoReq.getLastName());
			preparedStatement.setString(4, personalDetailsDtoReq.getMobileNo());
			preparedStatement.setString(5, personalDetailsDtoReq.getCity());
			preparedStatement.setString(6, personalDetailsDtoReq.getEmailId());
			preparedStatement.setString(7, personalDetailsDtoReq.getAddress());
			preparedStatement.setDate(8, dateofBirth);
			preparedStatement.setString(9, personalDetailsDtoReq.getGender());

			int executePersonalDetail = preparedStatement.executeUpdate();
			System.out.println(getClass()
					+ ":::personal details rows inserted:::"
					+ executePersonalDetail);

			// insert value in customer_details table
			preparedStatement = connection.prepareStatement(sqlOwnerDetails);
			preparedStatement.setString(1, userId);
			preparedStatement.setString(2, ownerDetailsDto.getSsn());
			preparedStatement.setString(3, ownerDetailsDto.getBloodGroup());
			preparedStatement.setString(4, ownerDetailsDto.getDrivingLicence());
			preparedStatement.setInt(5, ownerDetailsDto.getTotalAccident());
			int executeOwnerDetail = preparedStatement.executeUpdate();
			System.out.println(getClass()
					+ ":::customer details rows inserted:::"
					+ executeOwnerDetail);

			// /insert value in vehicle_details table
			preparedStatement = connection.prepareStatement(sqlVehicleDetails);
			preparedStatement.setString(1, userId);
			preparedStatement.setString(2, vehicleDetailsDto.getType());
			preparedStatement.setString(3, vehicleDetailsDto.getModel());
			preparedStatement.setString(4, vehicleDetailsDto.getMake());
			preparedStatement.setString(5, vehicleDetailsDto.getMfYear());
			preparedStatement.setString(6,
					vehicleDetailsDto.getVehicleRegistrationNo());
			preparedStatement.setString(7, vehicleDetailsDto.getPolicyType());
			preparedStatement.setInt(8, vehicleDetailsDto.getPolicyAmount());

			int executeVechileDetails = preparedStatement.executeUpdate();
			System.out.println(getClass()
					+ ":::vehicle details rows inserted:::"
					+ executeVechileDetails);

			// /insert value in users_login table
			preparedStatement = connection.prepareStatement(sqlUsersLogin);
			preparedStatement.setString(1, userId);
			preparedStatement.setString(2, loginDto.getPasswd());

			int executeLogin = preparedStatement.executeUpdate();
			System.out.println(getClass() + ":::login details rows inserted:::"
					+ executeLogin);

		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException e1) {
				System.err.println(getClass() + "::::" + e1.getMessage());
				return false;
			}
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
		}
		System.out.println(getClass() + "::::createUser::::end");
		return true;
	}

	/*
	 * Check whether the user already exists in the system before registering
	 * it.
	 * 
	 * @see com.cts.autoInsurance.dao.RegistrationDAO#isExistingUser(com.cts.
	 * autoInsurance.dto.LoginDto)
	 */
	@Override
	public boolean isExistingUser(LoginDto loginReq) {
		System.out.println(getClass() + "::::isExistingUser::::begin");
		Connection connection = getConnection();
		String sql = "SELECT * FROM USERS_LOGIN WHERE USER_ID=?";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, loginReq.getUserId());
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next())
				return true;
		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			return false;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
		}
		System.out.println(getClass() + "::::isExistingUser::::end");
		return false;
	}

	/*
	 * Calculate the Premium quotation for a user
	 * 
	 * @see com.cts.autoInsurance.dao.RegistrationDAO#getQuote(java.lang.String)
	 */
	@Override
	public Map<String, String> getQuote(String userId) {
		System.out.println(getClass() + "::::getQuote::::begin");
		Connection connection = getConnection();
		HashMap<String, String> map = new HashMap<String, String>();

		String sql = "SELECT FIRST_NAME,SSN,MAKE,MODEL,VEHICLE_REGISTRATION_NO," +
				"POLICY_TYPE,POLICY_AMOUNT,TOTAL_ACCIDENT,(SYSDATE-DATE_OF_BIRTH)/365 AGE" +
				" FROM REGISTRATION NATURAL JOIN VEHICLE_DETAILS NATURAL JOIN CUSTOMER_DETAILS" +
				" WHERE USER_ID=?";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, userId);
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				map.put("Name", resultSet.getString("FIRST_NAME"));
				map.put("SSN", resultSet.getString("SSN"));
				map.put("Make", resultSet.getString("MAKE"));
				map.put("Model", resultSet.getString("MODEL"));
				map.put("Vehicle Registration No",
						resultSet.getString("VEHICLE_REGISTRATION_NO"));
				map.put("Policy Type", resultSet.getString("POLICY_TYPE"));
				map.put("Policy Amount", resultSet.getString("POLICY_AMOUNT"));
				map.put("Total Accident", resultSet.getString("TOTAL_ACCIDENT"));
				map.put("Age",
						new Integer(Math.round(resultSet.getFloat("AGE")))
								.toString());
			}
			return map;
		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::getQuote::::end");
		}
	}

}
